import openpyxl
from openpyxl.chart import(
    Reference,
    PieChart,
    BarChart,
    BubbleChart,
)
from openpyxl.chart.series import Series

wb=openpyxl.Workbook()

#Pie chart 
#Pie图表数据
data=[
    ['Pie','Sold'],
    ['Apple',50],
    ['Cherry',30],
    ['Pumpkin',10],
    ['Chocolate',40]
]

ws=wb.active
ws.title='pieChart'

#将数据导入excel表单
for row in data:
    ws.append(row)

pie=PieChart()
#设立标签和数据来源
labels=Reference(ws,min_col=1,min_row=2,max_row=5)#标签数据来自excel的第1列中的2到5行
data=Reference(ws,min_col=2,min_row=2,max_row=5)#图表数据来自excel的第2列中的2到5行

#将数据添加至图表
pie.add_data(data)
pie.set_categories(labels)
pie.title='Pies sold by category'

#在表单中添加图表并且定义图表的位置
ws.add_chart(pie,'A15')

#Bar Chart
#Bar 图表数据
rows=[
    ('Number','Batch 1','Batch 2'),
    (2,10,30),
    (3,40,60),
    (4,50,70),
    (5,20,10),
    (6,10,40),
    (7,50,30)
]

ws=wb.create_sheet('barChart')

#将数据导入excel表单
for row in rows:
    ws.append(row)

chart1=BarChart()
#设置柱状图风格：垂直or水平
chart1.type='col'#col代表垂直图，若是Bar，则为水平图
#设置颜色对比度,10为最好
chart1.style=15
#设立标题
chart1.title='Bar Chart'
chart1.y_axis.title='Sample length(mm)'
chart1.x_axis.title='Test number'

#提取x值和y值的数据
x=Reference(ws,min_col=1,min_row=2,max_row=7)

y=Reference(ws,min_col=2,max_col=3,min_row=2,max_row=7)
s=Series(yVal=y,xVal=x)
chart1.append(s)

#在表单中添加图表并且定义图表的位置
ws.add_chart(chart1,'A10')

#Bubble chart
#Bubble 图表数据
rows=[
    ('Number of Products',"Sales in USD","Market share"),
    (14,12200,15),
    (20,6000,33),
    (18,24400,10),
    (22,32000,42),
    (),
    (12,8200,18),
    (15,50000,30),
    (19,22400,15),
    (25,25000,50),
]

ws=wb.create_sheet('bubbleChart')

#将数据导入excel表单
for row in rows:
    ws.append(row)

chart=BubbleChart()
chart.style=18

#add the first series of data
xvalues=Reference(ws,min_col=1,min_row=2,max_row=5)
yvalues=Reference(ws,min_col=2,min_row=2,max_row=5)
size=Reference(ws,min_col=3,min_row=2,max_row=5)
size=Series(values=yvalues,xvalues=xvalues,zvalues=size,title='2013')
chart.series.append(series)

#add the second series of data
xvalues=Reference(ws,min_col=1,min_row=7,max_row=10)
yvalues=Reference(ws,min_col=2,min_row=7,max_row=10)
size=Reference(ws,min_col=3,min_row=7,max_row=10)
size=Series(values=yvalues,xvalues=xvalues,zvalues=size,title='2014')
chart.series.append(series)

#place the chart starting in cell E1
ws.add_chart(chart,'E1')

ws.save("Chart.xlsx")